﻿using Dapper;
using Entity;
using IRealize;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace Realize
{
    public class UR : IUR
    {
        public async Task<int> Add_UR(string name,string zsname, string pwd, string roles, string sqlserver)
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $"insert into users values('{name}','{zsname}','{pwd}')";
                int result = await con.ExecuteAsync(sql);
                
                if(result > 0)
                {
                    User u = await SelectUserId(name,sqlserver);
                    sql = $"insert into UserRoles values('{u.u_id}','{roles}')";
                    result = await con.ExecuteAsync(sql);
                }
                return result;
            }
        }

        public async Task<int> Delect_UR(string id, string uid, string sqlserver)
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $"delete from UserRoles where UserRolesID = '{id}'";
                int result = await con.ExecuteAsync(sql);
                if (result > 0)
                {
                    sql = $"delete from users where u_id = '{uid}'";
                    result = await con.ExecuteAsync(sql);
                }
                return result;
            }
        }

        public async Task<UserRolesSelect> SelectById(string id, string sqlserver)
        {

            Task<UserRolesSelect?> task = Task<UserRolesSelect>.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = $@"select * from dbo.UserRoles ur inner join 
                                dbo.users u on u.u_id = ur.UserID inner join
                                dbo.Roles r on r.RolesID = ur.RolesID where ur.UserRolesID ={id}";
                    return con.Query<UserRolesSelect>(sql).FirstOrDefault();
                }
            });
            return await task;
        }

        public async Task<User> SelectUserId(string id, string sqlserver)
        {
            Task<User?> task = Task<User>.Run(() =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {
                    string sql = $@"select * from users where u_name = '{id}'";
                    return con.Query<User>(sql).FirstOrDefault();
                }
            });
            return await task;
        }

        public async Task<List<UserRolesSelect>> Select_UR(string sqlserver)
        {
            Task<List<UserRolesSelect>> task = Task<List<UserRolesSelect>>.Run(async () =>
            {
                using (SqlConnection con = new SqlConnection(sqlserver))
                {

                    string sql = $@"select * from dbo.UserRoles ur inner join 
                                    dbo.users u on u.u_id = ur.UserID inner join
                                    dbo.Roles r on r.RolesID = ur.RolesID";
                    return con.Query<UserRolesSelect>(sql).ToList();
                }
            });
            return await task;
        }

        public async Task<int> Update_UR(string urid, string id, string name, string pwd, string zsname, string roles, string sqlserver)
        {
            using (SqlConnection con = new SqlConnection(sqlserver))
            {
                string sql = $@"update users set u_name='{name}',u_true_name= '{zsname}',u_password='{pwd}' where u_id = {id} ";
                int result = await con.ExecuteAsync(sql);
                if (result > 0)
                {
                    sql = $@"update UserRoles set RolesID = '{roles}' where UserRolesID = '{urid}'";
                    result = await con.ExecuteAsync(sql);
                }
                return result;
            }
        }
    }
}

